IF OBJECT_ID('ssf.ptx_parse_task_group_run_id') IS NOT NULL
	DROP FUNCTION ssf.ptx_parse_task_group_run_id
GO

CREATE FUNCTION ssf.ptx_parse_task_group_run_id (
	@task_group_run_id NVARCHAR(256)
)
RETURNS TABLE
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

	RETURN (
		WITH parsed
		AS (
			SELECT 1 AS position
				, SUBSTRING(@task_group_run_id, CHARINDEX(',(', @task_group_run_id) + 1, LEN(@task_group_run_id)) AS string
				, SUBSTRING(@task_group_run_id, 2, CHARINDEX(')', @task_group_run_id) -2) AS element
			WHERE @task_group_run_id LIKE N'(_%),(_%),(_%),(_%)'
			UNION ALL
			SELECT position + 1
				, CASE WHEN CHARINDEX(',(', string) > 0
					THEN SUBSTRING(string, CHARINDEX(',(', string) + 1, LEN(string))
					ELSE ''
					END
				, CASE WHEN CHARINDEX(')', string) > 0
					THEN SUBSTRING(string, 2, CHARINDEX(')', string) -2)
					ELSE string
					END
			FROM parsed
			WHERE string <> ''
		)
		SELECT CAST([1] AS NVARCHAR(128)) AS group_type
			, CAST([2] AS NVARCHAR(128)) AS group_name
			, CAST([3] AS DATETIME) AS task_group_run_date
			, CAST([4] AS INT) AS task_group_spid
		FROM (
			SELECT position, element
			FROM parsed
		) AS parsed_rows
		PIVOT (
			MAX(element)
			FOR position IN ([1], [2], [3], [4])
		) AS parsed_columns
	)
GO
